/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Business;

import Connections.Database;
import Entities.BoothProduct;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.Currency;
import java.sql.Date;

/**
 *
 * @author manhnv_b00954
 */
public class BoothProductManage extends Database{
    Connection conn;
    public BoothProductManage() {
        conn = getConn();
    }
    public ArrayList<String> getCategory(){
        ArrayList<String> listCategory = new ArrayList<String>();
        try {
            String sql = "SELECT title FROM ProductCategory";
            PreparedStatement preStm = conn.prepareStatement(sql);
            ResultSet rs = preStm.executeQuery();
            while (rs.next()) {                
                listCategory.add(rs.getNString("title"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listCategory;
    }
    public ArrayList<String> getBooth(){
        ArrayList<String> listBooth = new ArrayList<String>();
        try {
            String sql = "SELECT name FROM BoothInformation";
            PreparedStatement preStm = conn.prepareStatement(sql);
            ResultSet rs = preStm.executeQuery();
            while (rs.next()) {                
                listBooth.add(rs.getNString("name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listBooth;
    }
    public ArrayList<String> getSizeUnit(){
        ArrayList<String> listSizeUnit = new ArrayList<String>();
        try {
            String sql = "SELECT symbol FROM SizeUnitMeasure";
            PreparedStatement preStm = conn.prepareStatement(sql);
            ResultSet rs = preStm.executeQuery();
            
            while (rs.next()) {                
                listSizeUnit.add(rs.getNString("symbol"));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listSizeUnit;
    }
    public ArrayList<String> getWeightUnit(){
        ArrayList<String> listWeightUnit = new ArrayList<String>();
        try {
            String sql = "SELECT symbol FROM WeighUnitMeasure";
            PreparedStatement preStm = conn.prepareStatement(sql);
            ResultSet rs = preStm.executeQuery();
            
            while (rs.next()) {                
                listWeightUnit.add(rs.getNString("symbol"));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listWeightUnit;
    }
    public boolean add(BoothProduct obj){
        boolean flag = false;
        String sql = "INSERT INTO [BoothProduct]"
                       +"([name]"
                       +",[cost]"
                       +",[size]"
                       +",[weight]"
                       +",[Description]"
                       +",[categoryID]"
                       +",[boothID]"
                       +",[weightUnitMeasureID]"
                       +",[sizeUnitMeasureID]"
                       +",[status]"
                       +",[modifiedDate])"
                       +"VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement preStm = conn.prepareStatement(sql);
            preStm.setNString(1, obj.getName());
            preStm.setFloat(2, obj.getCost());
            preStm.setString(3, obj.getSize());
            preStm.setInt(4, obj.getWeight());
            preStm.setNString(5, obj.getDescription());
            preStm.setInt(6, obj.getCategoryID());
            preStm.setInt(7, obj.getBoothID());
            preStm.setInt(8, obj.getWeightUnitMeasureId());
            preStm.setInt(9, obj.getSizeUnitMeasureId());
            preStm.setInt(10, obj.getStatus());
            preStm.setDate(11, obj.getModifiedDate());
            preStm.executeUpdate();
            flag = true;
        } catch (SQLException ex) {
            flag = false;
            System.out.println("Insert Product error !");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return flag;
    }
    public boolean update(BoothProduct obj){
        boolean flag = false;
        String sql = "UPDATE [BoothProduct]"
                   +"SET [name] = ?"
                      +",[cost] = ?"
                      +",[size] = ?"
                      +",[weight] = ?"
                      +",[Description] = ?"
                      +",[categoryID] = ?"
                      +",[boothID] = ?"
                      +",[weightUnitMeasureID] = ?"
                      +",[sizeUnitMeasureID] = ?"
                      +",[status] = ?"
                      +",[modifiedDate] = ?"
                 +" WHERE id =?";
        try {
            PreparedStatement preStm = conn.prepareStatement(sql);
            preStm.setNString(1, obj.getName());
            preStm.setFloat(2, obj.getCost());
            preStm.setString(3, obj.getSize());
            preStm.setInt(4, obj.getWeight());
            preStm.setNString(5, obj.getDescription());
            preStm.setInt(6, obj.getCategoryID());
            preStm.setInt(7, obj.getBoothID());
            preStm.setInt(8, obj.getWeightUnitMeasureId());
            preStm.setInt(9, obj.getSizeUnitMeasureId());
            preStm.setInt(10, obj.getStatus());
            preStm.setDate(11, obj.getModifiedDate());
            preStm.setInt(12, obj.getId());
            preStm.executeUpdate();
            flag = true;    
        } catch (SQLException ex) {
            System.out.println("Update Product error");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return flag;
    }
    public int getCategoryIdByName(String title){
        int idCategory = 0;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT id FROM ProductCategory WHERE title = ?");
            preStm.setString(1, title);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            idCategory = rs.getInt("id");
        } catch (SQLException ex) {
            System.out.println("Error getCategoryIdByName");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return idCategory;
    }
    public int getBoothIdByName(String name){
        int idBooth = 0;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT id FROM BoothInformation WHERE name = ?");
            preStm.setString(1, name);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            idBooth = rs.getInt("id");
        } catch (SQLException ex) {
            System.out.println("Error getBoothIdByName");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return idBooth;
    }
    public int getSizeIdByUnit(String symbol){
        int SizeId = 0;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT id FROM SizeUnitMeasure WHERE symbol = ?");
            preStm.setString(1, symbol);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            SizeId = rs.getInt("id");
        } catch (SQLException ex) {
            System.out.println("Error getSizeIdByUnit");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return SizeId;
    }
    public int getWeightIdByUnit(String symbol){
        int WeightId = 0;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT id FROM WeighUnitMeasure WHERE symbol = ?");
            preStm.setString(1, symbol);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            WeightId = rs.getInt("id");
        } catch (SQLException ex) {
            System.out.println("Error getWeightIdByUnit");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return WeightId;
    }
    public BoothProduct getProductById(int id){
        BoothProduct objProduct = new BoothProduct();
        String sql = "SELECT * FROM BoothProduct WHERE id = ?";
        try {
            PreparedStatement preStm = conn.prepareStatement(sql);
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            rs.next();

            objProduct.setName(rs.getNString("name"));
            objProduct.setCost(rs.getFloat("cost"));
            objProduct.setSize(rs.getString("size"));
            objProduct.setWeight(rs.getInt("weight"));
            objProduct.setDescription(rs.getNString("Description"));
            objProduct.setCategoryID(rs.getInt("categoryID"));;
            objProduct.setBoothID(rs.getInt("boothID"));
            objProduct.setWeightUnitMeasureId(rs.getInt("weightUnitMeasureID"));
            objProduct.setSizeUnitMeasureId(rs.getInt("sizeUnitMeasureID"));
            objProduct.setStatus(rs.getInt("status"));
            objProduct.setModifiedDate(rs.getDate("modifiedDate"));
            objProduct.setId(id);
        } catch (SQLException ex) {
            System.out.println("Error getProductById");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return objProduct;
    }
    public Object getWeightUnitById(int id){
        Object unit = null;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT symbol FROM WeighUnitMeasure WHERE id = ?");
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            unit = rs.getString("symbol");
        } catch (SQLException ex) {
            System.out.println("Error getWeightIdByUnit");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return unit;
    }
    public Object getBoothNameById(int id){
        Object booth = null;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT name FROM BoothInformation WHERE id = ?");
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            booth = rs.getString("name");
        } catch (SQLException ex) {
            System.out.println("Error getBoothIdByName");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return booth;
    }
    public Object getCategoryNameById(int id){
        Object category = null;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT title FROM ProductCategory WHERE id = ?");
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            category = rs.getString("title");
        } catch (SQLException ex) {
            System.out.println("Error getCategoryIdByName");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return category;
    }
    public Object getSizeUnitById(int id){
        Object unit = null;
        try {
            PreparedStatement preStm = conn.prepareCall("SELECT symbol FROM SizeUnitMeasure WHERE id = ?");
            preStm.setInt(1, id);
            ResultSet rs = preStm.executeQuery();
            rs.next();
            unit = rs.getString("symbol");
        } catch (SQLException ex) {
            System.out.println("Error getSizeIdByUnit");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return unit;
    }
//    public int getCountProduct(){
//        int count = 0;
//        String sql = "SELECT COUNT(*) AS number FROM BoothProduct";
//
//        try {
//            PreparedStatement preStm = conn.prepareStatement(sql);
//            ResultSet rs = preStm.executeQuery();
//            rs.next();
//            count = rs.getInt("number");
//            closeConn();
//        } catch (SQLException ex) {
//            System.out.println("Error getCountProduct");
//            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
//        }
//        return count;
//    }
    public ArrayList<BoothProduct> getList(){
        ArrayList<BoothProduct> arrList = new ArrayList<BoothProduct>();
        
        String sql = "SELECT * FROM BoothProduct  ORDER BY modifiedDate";
        try {
            PreparedStatement preStm = conn.prepareStatement(sql);
            //preStm.setInt(1, limitStart);
            //preStm.setInt(2, numbersShow);
            ResultSet rs = preStm.executeQuery();
            while(rs.next()){
                BoothProduct objProduct = new BoothProduct();
                objProduct.setName(rs.getNString("name"));
                objProduct.setCost(rs.getFloat("cost"));
                objProduct.setSize(rs.getString("size"));
                objProduct.setWeight(rs.getInt("weight"));
                objProduct.setDescription(rs.getNString("Description"));
                objProduct.setCategoryID(rs.getInt("categoryID"));;
                objProduct.setBoothID(rs.getInt("boothID"));
                objProduct.setWeightUnitMeasureId(rs.getInt("weightUnitMeasureID"));
                objProduct.setSizeUnitMeasureId(rs.getInt("sizeUnitMeasureID"));
                objProduct.setStatus(rs.getInt("status"));
                objProduct.setModifiedDate(rs.getDate("modifiedDate"));
                objProduct.setId(rs.getInt("id"));
                
                arrList.add(objProduct);
            }

        } catch (SQLException ex) {
            System.out.println("Error getList");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return arrList;
    }
    public void deleteById(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("DELETE BoothProduct WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error deleteById !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void block(int id){
        
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE BoothProduct set status = 1 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Block fail");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void unBlock(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE BoothProduct set status = 0 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("UnBlock fail");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
     public ArrayList<BoothProduct> search(String str){
        ArrayList<BoothProduct> arrList = new ArrayList<BoothProduct>();
        
        String sql = "SELECT * FROM BoothProduct WHERE name LIKE '%"+str+"%' ORDER BY name";
        try {
            PreparedStatement preStm = conn.prepareStatement(sql);
            ResultSet rs = preStm.executeQuery();
            while(rs.next()){
                BoothProduct objProduct = new BoothProduct();
                objProduct.setName(rs.getNString("name"));
                objProduct.setCost(rs.getFloat("cost"));
                objProduct.setSize(rs.getString("size"));
                objProduct.setWeight(rs.getInt("weight"));
                objProduct.setDescription(rs.getNString("Description"));
                objProduct.setCategoryID(rs.getInt("categoryID"));;
                objProduct.setBoothID(rs.getInt("boothID"));
                objProduct.setWeightUnitMeasureId(rs.getInt("weightUnitMeasureID"));
                objProduct.setSizeUnitMeasureId(rs.getInt("sizeUnitMeasureID"));
                objProduct.setStatus(rs.getInt("status"));
                objProduct.setModifiedDate(rs.getDate("modifiedDate"));
                objProduct.setId(rs.getInt("id"));
                
                arrList.add(objProduct);
            }

        } catch (SQLException ex) {
            System.out.println("Error search");
            Logger.getLogger(BoothProductManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return arrList;
    }
     
}
